package com.ssy.lingxi.report.dao.order;

import com.ssy.lingxi.report.model.bo.tally.statistics.ConfirmInquiryQuotationStatisticsTallyVO;
import com.ssy.lingxi.report.model.bo.tally.statistics.InquiryQuotationStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 询价报价DAO
 * @author 伍将
 * @date 2020/12/1
 * @version 2.0.0
 */
@Service
public class ProductQuotationDAO {

    @Autowired
    @Qualifier("orderJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取询价报价统计
     * @author 伍将
     * @date 2020/12/1
     * @return:
     **/
    public List<InquiryQuotationStatisticsTallyVO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" create_member_id member_id ,create_role_id role_id,")
                .append(" sum(case when interior_state in(1,6) or external_state =5 then 1 else 0 end) ToBeCommitValifyCount,")
                .append(" sum(case when interior_state = 2 then 1 else 0 end) ToBeValifyStep1Count,")
                .append(" sum(case when interior_state = 3 then 1 else 0 end) ToBeValifyStep2Count,")
                .append(" sum(case when interior_state = 4 then 1 else 0 end) ToBeCommitCount")
                .append(" from trade_quote")
                .append(" group by create_member_id,create_role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(InquiryQuotationStatisticsTallyVO.class));
    }

    /**
     * 获取确认询价报价统计
     * @author 伍将
     * @date 2020/12/1
     * @return:
     **/
    public List<ConfirmInquiryQuotationStatisticsTallyVO> getConfirmStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" member_id ,member_role_id role_id,")
                .append(" sum(case when confirm_interior_state in(1,6) then 1 else 0 end) ToBeCommitValifyCount,")
                .append(" sum(case when confirm_interior_state = 2 then 1 else 0 end) ToBeValifyStep1Count,")
                .append(" sum(case when confirm_interior_state = 3 then 1 else 0 end) ToBeValifyStep2Count,")
                .append(" sum(case when confirm_interior_state = 4 then 1 else 0 end) ToBeConfirmCount")
                .append(" from trade_quote")
                .append(" group by member_id,member_role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ConfirmInquiryQuotationStatisticsTallyVO.class));
    }
}
